package BankingApplication;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

public class DbAdapter 
{
	
	private ResultSet rsAccounts;
	private ResultSet rsAccountsFiltered;
	private ResultSet rsChecks;
	private ResultSet rsTrans;

	public DbAdapter()
	{
		Connection con = null;
		Statement st = null;
		
		Calendar cal = Calendar.getInstance();
		Date date = new Date();
		cal.setTime(date);
		cal.add(Calendar.DATE, -30);
		Date dateBefore30 = cal.getTime();
		SimpleDateFormat formatter = new SimpleDateFormat("yyyy mm dd");
		String formatedDate = formatter.format(dateBefore30);
		
		try 
		{
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection("jdbc:mysql://mentok.se.rit.edu:3306",
				"cme7761", "Och8Milapohf");

			
			if(!con.isClosed())
				System.out.println("Sucessfully connected to " + 
					"MySQL server using TCP/IP...");
				
			st = con.createStatement();
			
			rsAccountsFiltered = st.executeQuery("SELECT accountID, accountType, owner, balance" +
					"minbalance FROM accounts WHERE balance > 5000");
			
			rsAccounts = st.executeQuery("SELECT accountID, accountType, owner, balance" +
			"minbalance FROM accounts");
			
			rsChecks = st.executeQuery("SELECT checknum, to, amount, date WHERE date < " + formatedDate);
			
			rsTrans = st.executeQuery("SELECT type, payee, amount, date WHERE date < formatedDate");
			
			}
		catch (Exception e) 
		{
				System.err.println("Exception: " + e.getMessage());
		} 
		finally 
		{
			try 
			{
				if(con != null)
					con.close();
			}
			catch (SQLException e) {}
		}
	}
	
	public ResultSet getAccounts()
	{
		return rsAccounts;
	}
	
	public ResultSet getAccountsFiltered()
	{
		return rsAccountsFiltered;
	}
	
	public ResultSet getChecks()
	{
		return rsChecks;
	}
	
	public ResultSet getTransactions()
	{
		return rsTrans;
	}
}
